IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetExecutionDetails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetExecutionDetails]
GO
/******************************************************************            
* Name:		GetExecutionDetails            
* Purpose:  Get Information about protocol
* Note:		            
* PARAMETERS(IN)            
* Name				 Description                 
* -------------      -------------------------------------------            
* @executionId       Id to get all the information about protocol
*********************************************************************/            
CREATE PROCEDURE [dbo].[GetExecutionDetails]            
(            
	@executionId int
)            
AS BEGIN            
SET NOCOUNT ON            
            
	select distinct 
	pp.Name as SubmissionType,
	RTrim(Ltrim(ap1.FirstName)) + ' ' + Rtrim(Ltrim(ap1.LastName)) as SubmitterName,
	ap.FirstName + ' ' + ap.LastName as PIName,
	p.Title as ProtocolTitle,    
	pp.Id as ProtocolProcessId,    
    p.ModuleType,	
	fdo.FullyQualifiedName as SubmitterOrgName,
	fdo.FullyQualifiedName as OrgName,
	IsNull(p.ProtocolNumber,'NA') as ProtocolNumber,
	fdo1.ParentOrganizationId as PIOrgId,
	fdo1.FullyQualifiedName as PIOrgName,
	p.IsInterventionStudy,
	p.ProtocolType
	from ProtocolProcess pp
	inner join ProtocolProcess_Workflow_Map pwm
	on pwm.ProtocolProcessID = pp.Id
	inner join Protocol p
	on p.Id = pp.ProtocolId
	inner join AdmPerson ap
	on ap.Id = p.piPersonId
	inner join AdmPerson ap1
	on ap1.FwkDomainUserId = pp.SubmitterId
	left join swactivityList sal
	on sal.ExecutionId =  pwm.ExecutionId	
	left join AdmInternalPerson aip
	on aip.AdmPersonId = ap1.Id
	left join FwkDomainOrganization fdo
	on fdo.Id = aip.PrimaryOrganizationid
	inner join FwkDomainOrganization fdo1
	on fdo1.Id = p.PIFWkDomainOrganizationid	
	where pwm.ExecutionId = @executionId
		
END 